/* Given:
     * A 'named' PriceSheet key
     * An order date
     * A Product key
  
   Find the version of the named price sheet whose effective
   date encompasses the order date, and obtain the price break-related
   data for the Product.

   Results should all be of a particular price sheet version, but
   will vary by currency, uom, and minquantity.
*/
CREATE PROCEDURE [dbo].[asi_PrcQueryPriceBreak]
    @priceSheetKey uniqueidentifier,
    @orderDate datetime,
    @productKey uniqueidentifier
    AS
BEGIN
    SELECT sheet.Name, ver.PriceSheetVersionKey, ver.Version, ver.Adjustment, ver.IsAdjustmentPercent, verdet.IsVersionOwner, det.CurrencyCode, pbreak.UomKey, pbreak.UnitRate, pbreak.PriceBasisCode, pbreak.IsPercent, pbreak.MinQuantity
    FROM PriceSheet sheet
    INNER JOIN PriceSheetVersion ver ON sheet.PriceSheetKey=ver.PriceSheetKey
    INNER JOIN PriceVersionDetail verdet ON verdet.PriceSheetVersionKey=ver.PriceSheetVersionKey
    INNER JOIN PriceDetail det ON det.PriceDetailKey=verdet.PriceDetailKey
    INNER JOIN PriceBreak pbreak ON pbreak.PriceDetailKey=det.PriceDetailKey
    WHERE sheet.PriceSheetKey=@priceSheetKey
    AND (BeginDate<=@orderDate OR BeginDate IS NULL)
    AND (@orderDate <= EndDate OR EndDate IS NULL)
    AND ProductKey=@productKey
    ORDER BY sheet.PriceSheetKey, Version ASC, det.CurrencyCode, pbreak.UomKey, MinQuantity ASC
END

GO